using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.IO;

using MM.Entities;

namespace MM.Business
{
    public class clsTON_KHO
    {
        public clsTON_KHO(string sConn)
	    {
		    SqlHelper.ConnectionString = sConn;
        }
        #region private methods
        private TON_KHO GetDataRow(DataRow row)
        {
            TON_KHO objKHO = new TON_KHO();
            try
            {
                if (row["MaTonKho"] != DBNull.Value)
                    objKHO.MaTonKho = int.Parse(row["MaTonKho"].ToString());
            }
            catch { }
            try
            {
                if (row["MaChiTiet"] != DBNull.Value)
                    objKHO.MaChiTiet = int.Parse(row["MaChiTiet"].ToString());
            }
            catch { }
            try
            {
                if (row["MaTrangThai"] != DBNull.Value)
                    objKHO.MaTrangThai = int.Parse(row["MaTrangThai"].ToString());
            }
            catch { }
           
            try
            {
                if (row["MaKho"] != DBNull.Value)
                    objKHO.MaKho = (row["MaKho"].ToString());
            }
            catch { }
            try
            {
                if (row["TenKho"] != DBNull.Value)
                    objKHO.TenKho = (row["TenKho"].ToString());
            }
            catch { }
            try
            {
                if (row["SoLuong"] != DBNull.Value)
                    objKHO.SoLuong = float.Parse(row["SoLuong"].ToString());
            }
            catch { }
            try
            {
                if (row["MaVTPT"] != DBNull.Value)
                    objKHO.MaVTPT = (row["MaVTPT"].ToString());
            }
            catch { }
            try
            {
                if (row["TenVTPT"] != DBNull.Value)
                    objKHO.TenVTPT = (row["TenVTPT"].ToString());
            }
            catch { }
            try
            {
                if (row["TenTrangThai"] != DBNull.Value)
                    objKHO.TenTrangThai = (row["TenTrangThai"].ToString());
            }
            catch { }
            try
            {
                if (row["MaGiaoTacKho"] != DBNull.Value)
                    objKHO.MaGiaoTacKho = (row["MaGiaoTacKho"].ToString());
            }
            catch { }
            try
            {
                if (row["Ngay"] != DBNull.Value)
                    objKHO.Ngay = Convert.ToDateTime(row["Ngay"].ToString());
            }
            catch { }
            try
            {
                if (row["DauKy"] != DBNull.Value)
                    objKHO.DauKy = float.Parse(row["DauKy"].ToString());
            }
            catch { }
            try
            {
                if (row["Nhap"] != DBNull.Value)
                    objKHO.Nhap = float.Parse(row["Nhap"].ToString());
            }
            catch { }
            try
            {
                if (row["Xuat"] != DBNull.Value)
                    objKHO.Xuat = float.Parse(row["Xuat"].ToString());
            }
            catch { }
            try
            {
                if (row["CuoiKy"] != DBNull.Value)
                    objKHO.CuoiKy = float.Parse(row["CuoiKy"].ToString());
            }
            catch { }
            try
            {
                if (row["SoPhieu"] != DBNull.Value)
                    objKHO.SoPhieu = (row["SoPhieu"].ToString());
            }
            catch { }
            try
            {
                if (row["SoPhieuGoc"] != DBNull.Value)
                    objKHO.SoPhieuGoc = (row["SoPhieuGoc"].ToString());
            }
            catch { }
            try
            {
                if (row["SoPhieuHuy"] != DBNull.Value)
                    objKHO.SoPhieuHuy = (row["SoPhieuHuy"].ToString());
            }
            catch { }
            try
            {
                if (row["MaKhoThamChieuSAP"] != DBNull.Value)
                    objKHO.MaKhoThamChieuSAP = (row["MaKhoThamChieuSAP"].ToString());
            }
            catch { }
            try
            {
                if (row["ValuationType"] != DBNull.Value)
                    objKHO.ValuationType = (row["ValuationType"].ToString());
            }
            catch { }

            try
            {
                if (row["ThietYeu"] != DBNull.Value)
                    objKHO.ThietYeu = int.Parse(row["ThietYeu"].ToString().Trim());
            }
            catch { }

            try
            {
                if (row["DonViTinh"] != DBNull.Value)
                    objKHO.DonViTinh = (row["DonViTinh"].ToString());
            }
            catch { }

            return objKHO;
        }

        //private List<TON_KHO> GetTON_KHO_NEW(DataTable dt)
        //{
            
        //    List<TON_KHO> items = dt.AsEnumerable().Select(row => 
        //    new TON_KHO
        //        {
        //             = row.Field<string>("id"),
        //            name = row.Field<string>("name")
        //        }).ToList();
        //}

        private List<TON_KHO> GetTON_KHO(DataTable dt)
        {
            List<TON_KHO> lst = new List<TON_KHO>(dt.Rows.Count);         
           
            foreach(DataRow row in dt.Rows)
            {                
                lst.Add(this.GetDataRow(row));
            }
            return lst;
        }



        #endregion private methods

        #region public methods
        public List<TON_KHO> usp_SelectTON_KHO_ByMaKho(string MaKho,string MaVTPT,int MaTrangThai,DateTime? Fromdate, DateTime? Todate)
        {
            List<TON_KHO> lst = new List<TON_KHO>();
            DataTable dt = new DataTable();
            try
            {
                SqlHelper.OpenConnection();
                string spName = "usp_SelectTON_KHO_ByMaKho";
                SqlParameter[] par = new SqlParameter[5];
                par[0] = new SqlParameter("@MaKho", MaKho==""?null:MaKho);
                par[1] = new SqlParameter("@MaVTPT", MaVTPT==""?null:MaVTPT);
                par[2] = new SqlParameter("@MaTrangThai", MaTrangThai);
                par[3] = new SqlParameter("@NgayFrom", Fromdate);
                par[4] = new SqlParameter("@NgayTo", Todate);

                dt = SqlHelper.ExecuteToDataTable(spName, par);
                lst = this.GetTON_KHO(dt);
            }
            catch (Exception ex)
            {

            }
            finally
            {
                if (!SqlHelper.IsHaveTran())
                    SqlHelper.CloseConnection();
            }
            return lst;
        }

        public List<TON_KHO> usp_XemTON_KHO_Tonghop(TON_KHO obj)
        {
            List<TON_KHO> lst = new List<TON_KHO>();
            DataTable dt = new DataTable();
            try
            {
                SqlHelper.OpenConnection();
                string spName = "usp_XemTON_KHO_Tonghop";
                SqlParameter[] par = new SqlParameter[7];
                par[0] = new SqlParameter("@MaKho", obj.MaKho==""?null:obj.MaKho);
                par[1] = new SqlParameter("@MaVTPT", obj.MaVTPT==""?null:obj.MaVTPT);
                par[2] = new SqlParameter("@MaTrangThai", obj.MaTrangThai);
                par[3] = new SqlParameter("@FromDate", obj.FromDate);
                par[4] = new SqlParameter("@ToDate", obj.ToDate);
                par[5] = new SqlParameter("@MaNhomVTPT", obj.NhomVTPTFrom==""?null:obj.NhomVTPTFrom);
                if (obj.ThietYeu==2)
                    par[6] = new SqlParameter("@ThietYeu", null);
                else
                    par[6] = new SqlParameter("@ThietYeu", obj.ThietYeu);

                dt = SqlHelper.ExecuteToDataTable(spName, par);
                lst = this.GetTON_KHO(dt);
            }
            catch (Exception ex)
            {

            }
            finally
            {
                if (!SqlHelper.IsHaveTran())
                    SqlHelper.CloseConnection();
            }
            return lst;
        }

        public List<TON_KHO> usp_XemTON_KHO_Chitiet(TON_KHO obj)
        {
            List<TON_KHO> lst = new List<TON_KHO>();
            DataTable dt = new DataTable();
            try
            {
                SqlHelper.OpenConnection();
                string spName = "usp_XemTON_KHO_Chitiet";
                SqlParameter[] par = new SqlParameter[5];
                par[0] = new SqlParameter("@MaKho", obj.MaKho == "" ? null : obj.MaKho);
                par[1] = new SqlParameter("@MaVTPT", obj.MaVTPT == "" ? null : obj.MaVTPT);
                par[2] = new SqlParameter("@MaTrangThai", obj.MaTrangThai);
                par[3] = new SqlParameter("@FromDate", obj.FromDate);
                par[4] = new SqlParameter("@ToDate", obj.ToDate);

                dt = SqlHelper.ExecuteToDataTable(spName, par);
                lst = this.GetTON_KHO(dt);
            }
            catch (Exception ex)
            {

            }
            finally
            {
                if (!SqlHelper.IsHaveTran())
                    SqlHelper.CloseConnection();
            }
            return lst;
        }

        public void CsvExportTON_KHO_Tonghop(TON_KHO tk, List<TON_KHO> lst, string filepath)
        {
            StringBuilder sb = new StringBuilder();

            sb.Append("MATNR;");
            sb.Append(tk.MaVTPTFrom); sb.Append(";"); sb.Append(tk.MaVTPTTo);
            sb.AppendLine();
            sb.Append("MATKL;");
            sb.Append(tk.NhomVTPTFrom); sb.Append(";"); sb.Append(tk.NhomVTPTTo);
            sb.AppendLine();
            sb.Append("WERKS;");
            sb.Append(tk.PlantFrom); sb.Append(";"); sb.Append(tk.PlantTo);
            sb.AppendLine();
            sb.Append("LGORT;");
            sb.Append(tk.MaKhoFrom); sb.Append(";"); sb.Append(tk.MaKhoTo);
            sb.AppendLine();
            sb.Append("BUDAT;");
            string d = "";
            string m = "";
            string y = "";
            d = "0" + tk.FromDate.Day.ToString();
            d = d.Substring(d.Length - 2, 2);
            m = "0" + tk.FromDate.Month.ToString();
            m = m.Substring(m.Length - 2, 2);
            y = tk.FromDate.Year.ToString();

            sb.Append(d+"."+m+"."+y); 
            sb.Append(";");

            d = "0" + tk.ToDate.Day.ToString();
            d = d.Substring(d.Length - 2, 2);
            m = "0" + tk.ToDate.Month.ToString();
            m = m.Substring(m.Length - 2, 2);
            y = tk.ToDate.Year.ToString();
            sb.Append(d + "." + m + "." + y); 
           

            for (int i = 0; i < lst.Count; i++)
            {                
                sb.AppendLine();                  
               
                sb.Append(lst[i].MaVTPT);
                sb.Append(";");
                sb.Append(tk.PlantFrom);
                sb.Append(";");
                sb.Append(lst[i].MaKhoThamChieuSAP);
                sb.Append(";");
                sb.Append(lst[i].ValuationType);
                sb.Append(";");
                sb.Append(lst[i].DauKy);
                sb.Append(";");
                sb.Append(lst[i].Nhap);
                sb.Append(";");
                sb.Append(lst[i].Xuat);
                sb.Append(";");
                sb.Append(lst[i].CuoiKy);
            }
            

            File.WriteAllText(filepath, sb.ToString());
        }

        public void CsvExportTON_KHO_Chitiet(TON_KHO tk, List<TON_KHO> lst, string filepath)
        {
            StringBuilder sb = new StringBuilder();

            sb.Append("MATNR;");
            sb.Append(tk.MaVTPTFrom); sb.Append(";"); sb.Append(tk.MaVTPTTo);
            sb.AppendLine();
            sb.Append("MATKL;");
            sb.Append(tk.NhomVTPTFrom); sb.Append(";"); sb.Append(tk.NhomVTPTTo);
            sb.AppendLine();
            sb.Append("WERKS;");
            sb.Append(tk.PlantFrom); sb.Append(";"); sb.Append(tk.PlantTo);
            sb.AppendLine();
            sb.Append("LGORT;");
            sb.Append(tk.MaKhoFrom); sb.Append(";"); sb.Append(tk.MaKhoTo);
            sb.AppendLine();
            sb.Append("BUDAT;");
            string d = "";
            string m = "";
            string y = "";
            d = "0" + tk.FromDate.Day.ToString();
            d = d.Substring(d.Length - 2, 2);
            m = "0" + tk.FromDate.Month.ToString();
            m = m.Substring(m.Length - 2, 2);
            y = tk.FromDate.Year.ToString();

            sb.Append(d + "." + m + "." + y);
            sb.Append(";");

            d = "0" + tk.ToDate.Day.ToString();
            d = d.Substring(d.Length - 2, 2);
            m = "0" + tk.ToDate.Month.ToString();
            m = m.Substring(m.Length - 2, 2);
            y = tk.ToDate.Year.ToString();
            sb.Append(d + "." + m + "." + y);


            for (int i = 0; i < lst.Count; i++)
            {
                sb.AppendLine();

                sb.Append(lst[i].MaVTPT);
                sb.Append(";");
                sb.Append(tk.PlantFrom);
                sb.Append(";");
                sb.Append(lst[i].MaKhoThamChieuSAP);
                sb.Append(";");
                sb.Append(lst[i].ValuationType);
                sb.Append(";");
                sb.Append(lst[i].SoLuong);
                sb.Append(";");
                d = "0" + lst[i].Ngay.Day.ToString();
                d = d.Substring(d.Length - 2, 2);
                m = "0" + lst[i].Ngay.Month.ToString();
                m = m.Substring(m.Length - 2, 2);
                y = lst[i].Ngay.Year.ToString();

                sb.Append(d + "." + m + "." + y);
                sb.Append(";");
                sb.Append(lst[i].SoPhieu);
            }


            File.WriteAllText(filepath, sb.ToString());
        }
        
        #endregion public methods
    }
}
